--example to replace an 'in' clause by a join one

use AdventureWorksDW
CREATE TYPE SourceKeyList AS TABLE ( 
  SourceKey NVARCHAR(50)
)
GO
--check the created type
SELECT name, system_type_id, user_type_id  
FROM sys.types
WHERE is_table_type = 1
go

--create join table
create table testTable (k nvarchar(50))
go
insert into testTable 
select 'c1' union all
select 'c4'

insert into testTable values ('c5'), ('c7')
go

--sample store procedure for the example
CREATE PROCEDURE dbo.stp_GetCustomerSK
@source_key_list SourceKeyList READONLY
AS
BEGIN
	 select *
	 from @source_key_list 
	 where sourceKey in (select k from testTable)
END
GO

--use the example
DECLARE @source_key_list SourceKeyList

INSERT INTO @source_key_list
SELECT 'c1' UNION ALL
SELECT 'c2' UNION ALL
SELECT 'c3'

EXEC dbo.stp_GetCustomerSK @source_key_list
GO


--drop all creations
drop procedure dbo.stp_GetCustomerSK
drop type sourcekeylist
drop table testTable
go

 